<?php
/******************************
 * EQdkp
 * Copyright 2002-2005
 * Licensed under the GNU GPL.  See COPYING for full terms.
 * ------------------
 * upgrade.php
 * Began: Tue July 1 2003
 *
 *
 ******************************/

define('EQDKP_INC', true);
$eqdkp_root_path = './';
include_once($eqdkp_root_path . 'common.php');

    // I require MySQL version 4.0.4 minimum.
    $version = mysql_get_server_info();

    if ( ! ($version >= "4.0") ){

       printf("MySQL server version is not sufficient for EQdkp. ");
       printf("EQdkp and FFdkp require MySQL version > 4.0 - you are running \n");
       printf("version %s .\n", $version);
       die;
    }

class Upgrade
{
    var $db = null;
    var $eqdkp_versions = array('1.0.0','1.1.0','1.2.0B1','1.2.0B2','1.2.0RC1','1.2.0RC2','1.2.0','1.3.0','1.3.1');

    var $versions = array('0.1.1');

    function upgrade()
    {
        global $db, $eqdkp, $user, $tpl, $pm;
        global $SID;

        $db->error_die(false);

        if ( isset($_POST['upgrade']) )
        {
            // Find out what version we're upgrading from
            $version_from = $_POST['version'];
            foreach ( $this->versions as $index => $version )
            {
                if ( str_replace('.', '', $version) == $version_from )
                {
                    $method = 'upgrade_' . $version_from;
                    $this->$method($index);
                }
            }
        }
        else
        {
            $this->display_form();
        }
    }

    function finalize($index)
    {
        global $user;

        if ( isset($this->versions[$index + 1]) )
        {
            $method = 'upgrade_' . str_replace('.', '', $this->versions[$index + 1]);
            $this->$method($index + 1);
        }
        else
        {
            message_die($user->lang['upgrade_complete'], $user->lang['success']);
        }
    }

    //--------------------------------------------------------------
    // Upgrade methods
    //--------------------------------------------------------------
    function upgrade_011($index)
    {
        global $db, $eqdkp, $user, $tpl, $pm;
        global $SID, $table_prefix;

        // Build the new views
        $queries = array();

        $queries[] = "CREATE VIEW `".$table_prefix."v_member_data_lifetime` AS
    SELECT m.member_id,
           m.member_name,
           m.member_raidcount AS raid_count,
           m.member_raidcount / count( DISTINCT ra.raid_id ) AS raid_percent
    FROM eqdkp_raids ra, eqdkp_members m
    WHERE ra.raid_date >= m.member_firstraid
    GROUP BY m.member_id;";

        $queries[] = "CREATE VIEW `".$table_prefix."v_member_data_thirty` AS
    SELECT m.member_id, m.member_name, count( DISTINCT ra.raid_id ) AS raid_count, count( DISTINCT ra.raid_id ) / (
        SELECT count( DISTINCT raid_id )
        FROM eqdkp_raids ) AS raid_percent
    FROM eqdkp_members m
    LEFT JOIN eqdkp_raid_attendees ra ON m.member_name = ra.member_name
    LEFT JOIN eqdkp_raids r ON ra.raid_id = r.raid_id
    WHERE r.raid_date
        BETWEEN (
            UNIX_TIMESTAMP( ) - ( 86400 *30 )
        )
        AND UNIX_TIMESTAMP( )
    GROUP BY m.member_id;";

        $queries[] = "CREATE VIEW `".$table_prefix."v_member_data_sixty` AS
    SELECT m.member_id, m.member_name, count( DISTINCT ra.raid_id ) AS raid_count, count( DISTINCT ra.raid_id ) / (
        SELECT count( DISTINCT raid_id )
        FROM eqdkp_raids ) AS raid_percent
    FROM eqdkp_members m
    LEFT JOIN eqdkp_raid_attendees ra ON m.member_name = ra.member_name
    LEFT JOIN eqdkp_raids r ON ra.raid_id = r.raid_id
    WHERE r.raid_date
        BETWEEN (
            UNIX_TIMESTAMP( ) - ( 86400 *60 )
        )
        AND UNIX_TIMESTAMP( )
    GROUP BY m.member_id;";

        $queries[] = "CREATE VIEW `".$table_prefix."v_member_data_ninety` AS
    SELECT m.member_id, m.member_name, count( DISTINCT ra.raid_id ) AS raid_count, count( DISTINCT ra.raid_id ) / (
        SELECT count( DISTINCT raid_id )
        FROM eqdkp_raids ) AS raid_percent
    FROM eqdkp_members m
    LEFT JOIN eqdkp_raid_attendees ra ON m.member_name = ra.member_name
    LEFT JOIN eqdkp_raids r ON ra.raid_id = r.raid_id
    WHERE r.raid_date
        BETWEEN (
            UNIX_TIMESTAMP( ) - ( 86400 *90 )
        )
        AND UNIX_TIMESTAMP( )
    GROUP BY m.member_id;";

        foreach ($queries as $query)
        {
            $db->query($query);
        }

        unset($queries);

        // build the new member_class table
        $sql = "
        CREATE TABLE IF NOT EXISTS `".$table_prefix."member_classes` (
        `member_class_id` smallint(5) unsigned NOT NULL auto_increment,
        `member_id` smallint(5) unsigned NOT NULL,
        `class_id` bigint(3) unsigned NOT NULL,
        `secondary_class` tinyint(1) NOT NULL default '0',
        PRIMARY KEY  (`member_class_id`)
        ) AUTO_INCREMENT=1 ;";

        $db->query($sql);

        // Next, get the current class ids
        $classes = array();

        $sql = 'SELECT DISTINCT class_id FROM '.$table_prefix.'classes';
        $result = $db->query($sql);

        while ($row = $db->fetch_record($result))
        {
            $classes[] = $row['class_id'];
        }

        foreach ($classes as $class_id)
        {
            $member_ids = array();
            # Update for primary class
            $sql = "SELECT DISTINCT m.member_id FROM ".MEMBERS_TABLE." m
            WHERE (m.member_class_id & ".$class_id.") = '".$class_id."'";

            $result = $db->query($sql);

            while ($row = $db->fetch_record($result))
            {
                $member_ids[] = $row['member_id'];
            }

            foreach ($member_ids as $member_id)
            {
                if ($member_id >= 0)
                {
                    $insert_sql = "INSERT INTO ".$table_prefix."member_classes (member_id, class_id, secondary_class) VALUES ";
                    $insert_sql = $insert_sql."('".$member_id."','".$class_id."','0') ";
                    $db->query($insert_sql);
                }
            }

            unset($member_ids);

            # Update for secondary class
            $sql = "SELECT DISTINCT m.member_id FROM ".MEMBERS_TABLE." m
                WHERE (m.member_second_class_id & ".$class_id.") = '".$class_id."'";

            $result = $db->query($sql);

            while ($row = $db->fetch_record($result))
            {
                $member_ids[] = $row['member_id'];
            }

            foreach ($member_ids as $member_id)
            {
                if ($member_id >= 0)
                {
                    $insert_sql = "INSERT INTO ".$table_prefix."member_classes (member_id, class_id, secondary_class) VALUES ";
                    $insert_sql = $insert_sql."('".$member_id."','".$class_id."','1') ";
                    $db->query($insert_sql);
                }
            }
        }

        $this->finalize($index);
    }

    function upgrade_100($index)
    {
        global $db, $eqdkp, $user, $tpl, $pm;
        global $SID, $table_prefix;

        $queries = array(
            "CREATE TABLE IF NOT EXISTS " . $table_prefix . "member_flags (
               flag_id smallint(3) unsigned NOT NULL UNIQUE,
               flag_name varchar(50) NOT NULL));",
            "INSERT INTO " . $table_prefix . "member_flags (flag_id, flag_name) VALUES ('0', '');",
            "INSERT INTO " . $table_prefix . "member_flags (flag_id, flag_name) VALUES ('1', 'Member');",
            "ALTER TABLE " . $table_prefix . "members ADD member_flag smallint(3) NOT NULL default '0' AFTER member_class;",
            "INSERT INTO " . $table_prefix . "config (config_name, config_value) VALUES ('parsetags', '');",
            "INSERT INTO " . $table_prefix . "styles (style_id, style_name, template_path, body_background, body_link, body_link_style, body_hlink, body_hlink_style, header_link, header_link_style, header_hlink, header_hlink_style, tr_color1, tr_color2, th_color1, fontface1, fontface2, fontface3, fontsize1, fontsize2, fontsize3, fontcolor1, fontcolor2, fontcolor3, fontcolor_neg, fontcolor_pos, table_border_width, table_border_color, table_border_style, input_color, input_border_width, input_border_color, input_border_style) VALUES (10, 'dkpUA', 'default', '253546', 'C6C6C6', 'underline', '576695', 'underline', 'C6C6C6', 'none', 'C6C6C6', 'underline', '39495A', '283846', '1F2F3D', 'Verdana', 'Verdana', 'Verdana', 10, 11, 12, 'C6C6C6', 'C6C6C6', '000000', 'FF0000', '00C000', 1, '60707E', 'solid', 'FFFFFF', 1, '60707E', 'solid');",
            "INSERT INTO " . $table_prefix . "styles (style_id, style_name, template_path, body_background, body_link, body_link_style, body_hlink, body_hlink_style, header_link, header_link_style, header_hlink, header_hlink_style, tr_color1, tr_color2, th_color1, fontface1, fontface2, fontface3, fontsize1, fontsize2, fontsize3, fontcolor1, fontcolor2, fontcolor3, fontcolor_neg, fontcolor_pos, table_border_width, table_border_color, table_border_style, input_color, input_border_width, input_border_color, input_border_style) VALUES (11, 'subSilver', 'default', 'FFFFFF', '006699', 'underline', 'DD6900', 'underline', 'FFA34F', 'none', 'FFA34F', 'underline', 'DEE3E7', 'EFEFEF', '1073A5', 'Verdana, Arial', 'Verdana, Arial', 'Verdana, Arial', 10, 11, 12, '000000', '000000', '000000', 'F80000', '008800', 1, '006699', 'solid', 'FFFFFF', 1, '000000', 'solid');",
            "INSERT INTO " . $table_prefix . "styles (style_id, style_name, template_path, body_background, body_link, body_link_style, body_hlink, body_hlink_style, header_link, header_link_style, header_hlink, header_hlink_style, tr_color1, tr_color2, th_color1, fontface1, fontface2, fontface3, fontsize1, fontsize2, fontsize3, fontcolor1, fontcolor2, fontcolor3, fontcolor_neg, fontcolor_pos, table_border_width, table_border_color, table_border_style, input_color, input_border_width, input_border_color, input_border_style) VALUES (12, 'EQdkp VB2', 'default', 'FFFFFF', '000000', 'underline', 'FF4400', 'underline', 'FFF788', 'none', 'FFF788', 'underline', 'F1F1F1', 'DFDFDF', '8080A6', 'Verdana, Arial', 'Verdana, Arial', 'Verdana, Arial', 10, 11, 12, '000000', '000000', '000000', 'F80000', '008800', 1, '555576', 'solid', 'FFFFFF', 1, '000000', 'solid');",
            "INSERT INTO " . $table_prefix . "styles (style_id, style_name, template_path, body_background, body_link, body_link_style, body_hlink, body_hlink_style, header_link, header_link_style, header_hlink, header_hlink_style, tr_color1, tr_color2, th_color1, fontface1, fontface2, fontface3, fontsize1, fontsize2, fontsize3, fontcolor1, fontcolor2, fontcolor3, fontcolor_neg, fontcolor_pos, table_border_width, table_border_color, table_border_style, input_color, input_border_width, input_border_color, input_border_style) VALUES (13, 'EQCPS', 'default', '7B7984', '151F41', 'underline', '800000', 'underline', 'FFFFFF', 'none', 'FFFFFF', 'none', 'CECBCE', 'BDBABD', '424952', 'Verdana, Arial', 'Verdana, Arial', 'Verdana, Arial', 10, 11, 12, '000000', '000000', '000000', '800000', '008000', 1, '000000', 'solid', 'C0C0C0', 1, '000000', 'solid');",
            "INSERT INTO " . $table_prefix . "style_config (style_id, attendees_columns, logo_path) VALUES (10, '8', 'dkpua_logo.gif');",
            "INSERT INTO " . $table_prefix . "style_config (style_id, attendees_columns, logo_path) VALUES (11, '8', 'subsilver_logo.gif');",
            "INSERT INTO " . $table_prefix . "style_config (style_id, attendees_columns, logo_path) VALUES (12, '8', 'logo.gif');",
            "INSERT INTO " . $table_prefix . "style_config (style_id, attendees_columns, logo_path) VALUES (13, '8', 'logo.gif');"
        );

        foreach ( $queries as $sql )
        {
            $db->query($sql);
        }

        $this->finalize($index);
    }

    function display_form()
    {
        global $db, $eqdkp, $user, $tpl, $pm;
        global $SID;

        foreach ( $this->versions as $version )
        {
            // This will never happen if common.php's been upgraded already; I'm a re-re!
            $selected = ( $version == EQDKP_VERSION ) ? ' selected="selected"' : '';

            $tpl->assign_block_vars('version_row', array(
                'VALUE'    => str_replace('.', '', $version),
                'SELECTED' => $selected,
                'OPTION'   => 'FFdkp ' . $version)
            );
        }

        $tpl->assign_vars(array(
            'L_EQDKP_UPGRADE'  => $user->lang['eqdkp_upgrade'],
            'L_SELECT_VERSION' => $user->lang['select_version'],
            'L_UPGRADE'        => $user->lang['upgrade'])
        );

        $eqdkp->set_vars(array(
            'page_title'    => $user->lang['eqdkp_upgrade'],
            'template_file' => 'upgrade.html',
            'display'       => true)
        );
    }
}

$upgrade = new Upgrade();

// And the upgrade-o-matic 5000 takes care of the rest.
?>
